package my_web.service;
import java.util.ArrayList;
import java.util.List;
import dao.dao.Dao;
import dao.dao.PreSQL;
import dao.dao.impl.DaoFactory;
import dao.dao.impl.DaoImp;
import my_web.Interface.EmpInterface;
import my_web.bean.Dep;
import my_web.bean.Emp;
import my_web.bean.Pos;
import my_web.bean.User;
import my_web.model.EmpDto;
import my_web.page.Page;
import my_web.page.PageEngine;
import my_web.page.PageParam;
import my_web.page.Imp.PageEngineFactory;
import my_web.page.Imp.PageImp;
public class EmpService implements EmpInterface {
	public List<Emp> getAllEmps() {
		Dao dao=DaoFactory.getCurrDao();
		String sql=
					"select E.e_id,D.d_name,E.e_name,E.e_state,P.p_name from Emp_table E "
					+ "left join dep_table D on E.d_id=D.d_id "
					+ "left join pos_table P on E.p_id=P.p_id "
					+ "order by E.e_id";
		return dao.queryBeanList(Emp.class, sql);	
	}
	public Page getEmpPage(PageParam pageParam) {
		Dao dao=DaoFactory.getCurrDao();
		return new PageImp(
				new PageEngine(){
					public int getTotal() {
						return dao.queryInteger("select count(e_id) from Emp_table");
					}
					public List<?> getReclist(int start, int end) {
						String sql=
								"select * "+ 
								"from "+ 
								"(select E.*,rownum row_ from"+ 
								/*Emp_table */
								"(select E.e_id,D.d_name,E.e_name,E.e_state,P.p_name from Emp_table E left join dep_table D on E.d_id=D.d_id left join pos_table P on E.p_id=P.p_id order by E.e_id)"
								+"E where rownum <="+end+")t0 where row_>"+start;
						return dao.queryBeanList(Emp.class, sql);
					}
				},
				pageParam
			);
	}
	public void addEmp(EmpDto dto) {
		Dao dao=DaoFactory.getCurrDao();
		dao.update("insert into Emp_table(e_id,d_id,e_name,e_state,p_id)values(?,?,?,?,?)", 
					dto.getE_id(),dto.getD_id(),dto.getE_name(),dto.getE_state(),dto.getP_id());

	}
	public List<Pos> getAllPoss() {
		Dao dao=DaoFactory.getCurrDao();
		return dao.queryBeanList(Pos.class, "select *from pos_table");
	}
	public List<Dep> getAllDeps() {
		Dao dao=DaoFactory.getCurrDao();
		return dao.queryBeanList(Dep.class, "select *from dep_table");
	}
	public void removeEmpById(String e_id) {
		Dao dao=DaoFactory.getCurrDao();
		dao.update("delete from Emp_table where e_id=?",e_id);
	}
	public Emp getEmpById(String e_id) {
		Dao dao=DaoFactory.getCurrDao();
		return  dao.queryBean(Emp.class,"select * "
					+ "from Emp_table where e_id=?",e_id);
	}
	public void modifyEmp(EmpDto dto) {
		Dao dao=DaoFactory.getCurrDao();
		dao.update("update Emp_table set d_id=?,e_name=?,e_state=?,p_id=?where e_id=?",dto.getD_id(),dto.getE_name(),dto.getE_state(),dto.getP_id(),dto.getE_id());
	}
	public void removeEmpById(String[] e_ids) {
		if(e_ids==null)return;
		Dao dao=DaoFactory.getCurrDao();
		PreSQL psql=dao.preSql("delete from Emp_table where e_id=?");
		for(String e_id:e_ids){
			psql.update(e_id);
		}
	}
	/**
	 * 动态查询
	 */
	public Page DynamicQueryAllEmps(EmpDto dto) {
		Dao dao=DaoFactory.getCurrDao();
		/**
		 * 1:获取查询条件对应的完整sql语句
		 */
		String sql=
				"select E.e_id,D.d_name,E.e_name,E.e_state,P.p_name from Emp_table E "
				+ "left join dep_table D on E.d_id=D.d_id "
				+ "left join pos_table P on E.p_id=P.p_id "
				+ "where 1=1";
		StringBuilder where=new StringBuilder();
		List<Object> paramList=new ArrayList<>();
		if(dto!=null){
			if(dto.getE_id()!=null && !dto.getE_id().equals("")){
				where.append(" and e_id like ?");
				paramList.add("%"+dto.getE_id()+"%");
			}
			if(dto.getD_name()!=null && !dto.getD_name().equals("")){
				where.append(" and d_name like ?");
				paramList.add("%"+dto.getD_name()+"%");
			}
			if(dto.getE_name()!=null && !dto.getE_name().equals("")){
				where.append(" and e_name like ?");
				paramList.add("%"+dto.getE_name()+"%");
			}
			if(dto.getE_state()!=null && !dto.getE_state().equals("")){
				where.append(" and e_state like ?");
				paramList.add("%"+dto.getE_state()+"%");
			}
			if(dto.getP_name()!=null && !dto.getP_name().equals("")){
				where.append(" and p_name like ?");
				paramList.add("%"+dto.getP_name()+"%");
			}
		}
		where=where.append("order by e_id");
		try{
			return PageEngineFactory.createOraclePage(Emp.class,dao,sql+where,dto,paramList.toArray());
		}catch(RuntimeException e){
			throw e;
		}
	}
	@Override
	public void updateImages(Emp emp) {
		DaoFactory.getCurrDao().update("update Emp_table set image=? where e_id=?", emp.getImage(),emp.getE_id());
		
	}
}